package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.AnrrsmEntity;
import com.css.fxfzypg.modules.baseData.queryParams.SchoolQueryParams;
import com.css.fxfzypg.modules.baseData.repository.SchoolRepository;
import com.css.fxfzypg.modules.baseData.entity.SchoolEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-15 17:20
 */
@Repository
public class SchoolRepositoryImpl implements SchoolRepository {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public SchoolEntity saveSchool(SchoolEntity schoolEntity) {
      /*  String sql = "";
        sql = "insert into data_school values(:schReslongitude, :schReslatitude, :schName, :schType, :schStuNum, :schIsboarding, :schCreatetime" +
                ", :schUpdatetime, :schIsdelete) ";

        SqlParameterSource source = new BeanPropertySqlParameterSource(schoolEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);*/
    /*  String sql="";
      sql="insert into data_school (sch_reslongitude,sch_reslatitude,sch_name,sch_type,sch_stu_num,sch_isboarding,sch_createtime,sch_isdelete) values(?,?,?,?,?,?,?,?)";
      jdbcTemplate.update(sql, schoolEntity.getSchReslongitude(),
                schoolEntity.getSchReslatitude(),
                schoolEntity.getSchName(),
                schoolEntity.getSchType(),
                schoolEntity.getSchStuNum(),
                schoolEntity.getSchIsboarding(),
                schoolEntity.getSchCreatetime(),
                schoolEntity.getSchIsdelete());

        return schoolEntity;*/
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into data_school (sch_reslongitude,sch_reslatitude,sch_name,sch_type,sch_stu_num,sch_isboarding,sch_createtime,sch_isdelete,sch_create_user) values(?,?,?,?,?,?,?,?,?)";
                //  sql="insert into data_school values(null,?,?,?,?,?,?,?,null ,?)";

                //String sql_sms = "insert into  sms(title,content,date_s,form,sffs,by1,by2,by3) values (?,?,'"+dates+"',?,?,?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String[]{"sch_id"});
                ps.setFloat(1, schoolEntity.getSchReslongitude());
                ps.setFloat(2, schoolEntity.getSchReslatitude());
                ps.setString(3, schoolEntity.getSchName());
                ps.setString(4, schoolEntity.getSchType());
                ps.setInt(5, schoolEntity.getSchStuNum());
                ps.setString(6, schoolEntity.getSchIsboarding());
                Date createDate = new Date(schoolEntity.getSchCreatetime().getTime());
                ps.setDate(7, createDate);
                ps.setString(8, schoolEntity.getSchIsdelete());
                ps.setString(9, schoolEntity.getSchCreateUser());
                return ps;
            }
        }, keyHolder);

        int i = keyHolder.getKey().intValue();
        schoolEntity.setSchId(i);
        return schoolEntity;

    }

    @Override
    public SchoolEntity findSchoolById(String dataSchoolId) {
        String sql = "";
        sql = "select  u.* from data_school u where sch_Id = '" + dataSchoolId + "' and sch_isdelete='" + YNEnum.N.toString() + "'";

        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SchoolEntity.class));
    }

    @Override
    public Map<String, Object> getAllSchoolByParams(SchoolQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_school u ");

        //  StringBuilder orderSql = new StringBuilder(" order by u.sort");

        StringBuilder whereSql = new StringBuilder(" where u.sch_isdelete = '" + YNEnum.N.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getSchReslongitude())) {
            whereSql.append(" and u.sch_reslongitude like %" + queryParams.getSchReslongitude() + "%");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getSchReslatitude())) {
            whereSql.append(" and u.sch_reslatitude like %" + queryParams.getSchReslatitude() + "%");
        }

        int firstIndex = (queryParams.getCurPage() - 1) * queryParams.getPageSize();
        int lastIndex = queryParams.getCurPage() * queryParams.getPageSize();
        sql.append(whereSql).toString();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SchoolEntity.class)).size();
        List<SchoolEntity> schoolEntities = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SchoolEntity.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", schoolEntities);
        return map;
    }

    @Override
    public void deleteSchool(List<Integer> ids, Timestamp currentTimestamp, String userID) {
        String sql = "update data_school set sch_isdelete = '" + YNEnum.Y.toString() + "' ,sch_updatetime ='" + currentTimestamp + "' ,sch_update_user='" + userID + "'" +
                " where sch_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<SchoolEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_school u where u.sch_isdelete='" + YNEnum.N.toString() + "' ", new BeanPropertyRowMapper<>(SchoolEntity.class));
    }

    @Override
    public void importExcel(List<SchoolEntity> schoolEntities) {
        jdbcTemplate.batchUpdate("insert into data_school (sch_reslongitude,sch_reslatitude,sch_name,sch_type,sch_stu_num,sch_isboarding,sch_createtime,sch_isdelete)values(?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1, schoolEntities.get(i).getSchReslongitude());
                ps.setFloat(2, schoolEntities.get(i).getSchReslatitude());
                ps.setString(3, schoolEntities.get(i).getSchName());
                ps.setString(4, schoolEntities.get(i).getSchType());
                ps.setFloat(5, schoolEntities.get(i).getSchStuNum());
                ps.setString(6, schoolEntities.get(i).getSchIsboarding());
                Date createDate = new Date(schoolEntities.get(i).getSchCreatetime().getTime());
                ps.setDate(7, createDate);
                ps.setString(8, schoolEntities.get(i).getSchIsdelete());
            }

            @Override
            public int getBatchSize() {
                return schoolEntities.size();
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_school where county = '" + county + "' and city = '" + city + "' and province = '" + province + "'  or (province ='"+province+"' and city ='' and county ='') and sch_isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_school where city = '" + city + "' and province = '" + province + "' or (province ='"+province+"' and city ='' and county ='')  and sch_isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_school where province = '" + province + "' and sch_isdelete = '0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_school where province = ? and (TO_CHAR(sch_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(sch_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and sch_isdelete = '0'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
